package cn.jgls.merdoc;

import java.math.BigDecimal;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;

import java.sql.SQLException;
import java.util.List;
import com.ibatis.sqlmap.client.SqlMapClient;
import cn.jgls.merdoc.model.*;

import java.io.FileInputStream;
import java.io.File;

public class MakeDeptSaleYear {

    private BigDecimal dept;
    private String ls_year;
    private String ls_month;
    private String ls_realpath;
    private String ls_plmc;
    private String ls_dept;

    public BigDecimal getDept() {
        return dept;
    }

    public void setDept(BigDecimal dept) {
        this.dept = dept;
    }

    public String getYear() {
        return ls_year;
    }

    public void setYear(String ls_year) {
        this.ls_year = ls_year;
    }

    public String getMonth() {
        return ls_month;
    }

    public void setMonth(String ls_month) {
        this.ls_month = ls_month;
    }

    public String getPath() {
        return ls_realpath;
    }

    public void setPath(String ls_realpath) {
        this.ls_realpath = ls_realpath;
    }

    public String getPlmc() {
        return ls_plmc;
    }

    public void setPlmc(String ls_plmc) {
        this.ls_plmc = ls_plmc;
    }

    public String getSDept() {
        return ls_dept;
    }

    public void setSDept(String ls_dept) {
        this.ls_dept = ls_dept;
    }
    private String outputFile = "";

    // public static String outputFile = "test20070212.xls";
    public static String fileToBeRead = "e:\\test.xls";

    public void cExcel() {
        outputFile = ls_realpath + "//xls//" + ls_year + ".xls";
        boolean exists = (new File(outputFile)).exists();
        if (exists) {
            // File or directory exists
            // System.out.println("文件已存在...");
            exists = (new File(outputFile)).delete();
            createExcel();
        } else {
            // File or directory does not exist
            // System.out.println("文件不存在...");
            createExcel();
        }

    }

    public void createExcel() {
        short ii;
        HSSFCell cell0;
        HSSFCell cell1;
        HSSFCell cell2;
        HSSFCell cell3;
        HSSFCell cell4;
        HSSFCell cell5;
        HSSFCell cell6;
        HSSFCell cell7;
        HSSFCell cell8;
        HSSFCell cell9;
        HSSFCell cell10;
        HSSFCell cell11;
        HSSFCell cell12;
        HSSFCell cell13;
        HSSFCell cell14;
        HSSFCell cell15;
        HSSFCell cell16;
        HSSFCell cell17;
        int li_rowcount;

        HSSFRow row;
        try {
            outputFile = ls_realpath + "//xls//" + ls_year + ".xls";
            // create a cell style object 创建一个元素的样式对象
            // 创建新的Excel 工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 在Excel工作簿中建一工作表，其名为缺省值
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFCellStyle cellStyle2 = workbook.createCellStyle();
            HSSFCellStyle cellStyle3 = workbook.createCellStyle();
            // set cell border 设置元素的边框
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            // cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            // cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            HSSFFont font1 = workbook.createFont();
            font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 此处的文字大小为实际excel中的20倍即可
            font1.setFontHeight((short) 280);
            cellStyle.setFont(font1);

            HSSFSheet sheet = workbook.createSheet();
            // 设置sheet的名字
            workbook.setSheetName(0, "年度汇总", HSSFWorkbook.ENCODING_UTF_16);// 设置中文编码；
            // 在索引0的位置创建行（最顶端的行
            // 第一行，标题
            ii = 0;
            row = sheet.createRow(ii);
            cell0 = row.createCell((short) 0);
            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);// 这里是设置编码保证中文正常显示
            cell0.setCellValue(ls_year + "年各品类销售明细");
            cell0.setCellStyle(cellStyle);
            cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            // 第二行，表头
            ii++;
            row = sheet.createRow(ii);
            cell0 = row.createCell((short) 0);
            cell1 = row.createCell((short) 1);
            cell2 = row.createCell((short) 2);
            cell3 = row.createCell((short) 3);
            cell4 = row.createCell((short) 4);
            cell5 = row.createCell((short) 5);
            cell6 = row.createCell((short) 6);
            cell7 = row.createCell((short) 7);
            cell8 = row.createCell((short) 8);
            cell9 = row.createCell((short) 9);
            cell10 = row.createCell((short) 10);
            cell11 = row.createCell((short) 11);
            cell12 = row.createCell((short) 12);
            cell13 = row.createCell((short) 13);
            cell14 = row.createCell((short) 14);
            cell15 = row.createCell((short) 15);
            cell16 = row.createCell((short) 16);
            cell17 = row.createCell((short) 17);

            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);// 这里是设置编码保证中文正常显示
            cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell4.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell5.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell9.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell10.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell11.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell16.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell17.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell0.setCellValue("品类");
            cell1.setCellValue("新品种号编码");
            cell2.setCellValue("新品种号名称");
            cell3.setCellValue("类编");
            cell4.setCellValue("名称");
            cell5.setCellValue("合计");
            cell6.setCellValue("1月份");
            cell7.setCellValue("2月份");
            cell8.setCellValue("3月份");
            cell9.setCellValue("4月份");
            cell10.setCellValue("5月份");
            cell11.setCellValue("6月份");
            cell12.setCellValue("7月份");
            cell13.setCellValue("8月份");
            cell14.setCellValue("9月份");
            cell15.setCellValue("10月份");
            cell16.setCellValue("11月份");
            cell17.setCellValue("12月份");

            cell0.setCellStyle(cellStyle2);
            cell1.setCellStyle(cellStyle2);
            cell2.setCellStyle(cellStyle2);
            cell3.setCellStyle(cellStyle2);
            cell4.setCellStyle(cellStyle2);
            cell5.setCellStyle(cellStyle2);
            cell6.setCellStyle(cellStyle2);
            cell7.setCellStyle(cellStyle2);
            cell8.setCellStyle(cellStyle2);
            cell9.setCellStyle(cellStyle2);
            cell10.setCellStyle(cellStyle2);
            cell11.setCellStyle(cellStyle2);
            cell12.setCellStyle(cellStyle2);
            cell13.setCellStyle(cellStyle2);
            cell14.setCellStyle(cellStyle2);
            cell15.setCellStyle(cellStyle2);
            cell16.setCellStyle(cellStyle2);
            cell17.setCellStyle(cellStyle2);

            // 第三行，数据开始
            cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);
            // 从数据库中取数据
            DeptSale lds_tmp = new DeptSale();
            lds_tmp.setYear(ls_year);
            SqlMapClient sqlMap = MySqlMapConfig.getSqlMapInstance();

            List Data = null;
            try {
                int li_year = Integer.parseInt(ls_year);
                if (li_year < 2010) {
                    Data = sqlMap.queryForList("getPqSaleYearOld", lds_tmp);
                } else {
                    Data = sqlMap.queryForList("getPqSaleYear", lds_tmp);
                }



            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
            li_rowcount = Data.size();
            for (int i = 0; i < li_rowcount; i++) {

                ii = setExcelCell(ii, cellStyle3, sheet, Data, i);
            }
            /*
             * //在索引0的位置创建单元格（左上端 HSSFCell cell = row.createCell((short)0); //
             * 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); //
             * 在单元格中输入一些内容 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
             * cell.setCellValue("这也是测试"); //下一行 row=sheet.createRow( (short)1);
             * cell=row.createCell( (short)0); //设置单元格内容 cell.setCellValue(
             * "Powered by 夏天"); //下一行 row=sheet.createRow( (short)1);
             * cell=row.createCell( (short)1); //设置单元格内容 cell.setCellValue(
             * "kkkk by gggg");
             */
            // 新建一输出文件流
            FileOutputStream fOut = new FileOutputStream(outputFile);
            // 把相应的Excel 工作簿存盘
            workbook.write(fOut);
            fOut.flush();
            // 操作结束，关闭文件
            fOut.close();
        // System.out.println("文件生成...");
        } catch (Exception e) {
            System.out.println("已运行 xlCreate() : " + e);
        }
    }

    public void createExcelAll() {
        short ii;
        HSSFCell cell0;
        HSSFCell cell1;
        HSSFCell cell2;
        HSSFCell cell3;
        HSSFCell cell4;
        HSSFCell cell5;
        HSSFCell cell6;
        HSSFCell cell7;
        HSSFCell cell8;
        HSSFCell cell9;
        HSSFCell cell10;
        HSSFCell cell11;
        HSSFCell cell12;
        HSSFCell cell13;
        HSSFCell cell14;
        HSSFCell cell15;
        HSSFCell cell16;
        HSSFCell cell17;
        HSSFCell cell18;
        HSSFCell cell19;
        HSSFCell cell20;
        HSSFCell cell21;
        HSSFCell cell22;
        HSSFCell cell23;
        HSSFCell cell24;
        HSSFCell cell25;
        HSSFCell cell26;
        HSSFCell cell27;
        HSSFCell cell28;
        HSSFCell cell29;
        HSSFCell cell30;
        HSSFCell cell31;
        HSSFCell cell32;
        HSSFCell cell33;
        HSSFCell cell34;
        HSSFCell cell35;
        HSSFCell cell36;
        HSSFCell cell37;
        HSSFCell cell38;
        HSSFCell cell39;
        HSSFCell cell40;
        HSSFCell cell41;
        HSSFCell cell42;
        HSSFCell cell43;
        int li_rowcount;

        HSSFRow row;
        try {
            outputFile = ls_realpath + "//xls//" + ls_year + "all.xls";
            // create a cell style object 创建一个元素的样式对象
            // 创建新的Excel 工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 在Excel工作簿中建一工作表，其名为缺省值
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFCellStyle cellStyle2 = workbook.createCellStyle();
            HSSFCellStyle cellStyle3 = workbook.createCellStyle();
            // set cell border 设置元素的边框
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            // cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            // cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            HSSFFont font1 = workbook.createFont();
            font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 此处的文字大小为实际excel中的20倍即可
            font1.setFontHeight((short) 280);
            cellStyle.setFont(font1);

            HSSFSheet sheet = workbook.createSheet();
            // 设置sheet的名字
            workbook.setSheetName(0, "年度汇总", HSSFWorkbook.ENCODING_UTF_16);// 设置中文编码；
            // 在索引0的位置创建行（最顶端的行
            // 第一行，标题
            ii = 0;
            row = sheet.createRow(ii);
            cell0 = row.createCell((short) 0);
            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);// 这里是设置编码保证中文正常显示
            cell0.setCellValue(ls_year + "年各品类汇总明细");
            cell0.setCellStyle(cellStyle);
            cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            // 第二行，表头
            ii++;
            row = sheet.createRow(ii);
            cell0 = row.createCell((short) 0);
            cell1 = row.createCell((short) 1);
            cell2 = row.createCell((short) 2);
            cell3 = row.createCell((short) 3);
            cell4 = row.createCell((short) 4);
            cell5 = row.createCell((short) 5);
            cell6 = row.createCell((short) 6);
            cell7 = row.createCell((short) 7);
            cell8 = row.createCell((short) 8);
            cell9 = row.createCell((short) 9);
            cell10 = row.createCell((short) 10);
            cell11 = row.createCell((short) 11);
            cell12 = row.createCell((short) 12);
            cell13 = row.createCell((short) 13);
            cell14 = row.createCell((short) 14);
            cell15 = row.createCell((short) 15);
            cell16 = row.createCell((short) 16);
            cell17 = row.createCell((short) 17);
            cell18 = row.createCell((short) 18);
            cell19 = row.createCell((short) 19);
            cell20 = row.createCell((short) 20);
            cell21 = row.createCell((short) 21);
            cell22 = row.createCell((short) 22);
            cell23 = row.createCell((short) 23);
            cell24 = row.createCell((short) 24);
            cell25 = row.createCell((short) 25);
            cell26 = row.createCell((short) 26);
            cell27 = row.createCell((short) 27);
            cell28 = row.createCell((short) 28);
            cell29 = row.createCell((short) 29);
            cell30 = row.createCell((short) 30);
            cell31 = row.createCell((short) 31);
            cell32 = row.createCell((short) 32);
            cell33 = row.createCell((short) 33);
            cell34 = row.createCell((short) 34);
            cell35 = row.createCell((short) 35);
            cell36 = row.createCell((short) 36);
            cell37 = row.createCell((short) 37);
            cell38 = row.createCell((short) 38);
            cell39 = row.createCell((short) 39);
            cell40 = row.createCell((short) 40);
            cell41 = row.createCell((short) 41);
            cell42 = row.createCell((short) 42);
            cell43 = row.createCell((short) 43);


            cell0.setEncoding(HSSFCell.ENCODING_UTF_16);// 这里是设置编码保证中文正常显示
            cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell4.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell5.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell9.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell10.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell11.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell16.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell17.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell18.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell19.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell20.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell21.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell22.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell23.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell24.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell25.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell26.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell27.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell28.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell29.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell30.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell31.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell32.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell33.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell34.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell35.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell36.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell37.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell38.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell39.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell40.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell41.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell42.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell43.setEncoding(HSSFCell.ENCODING_UTF_16);

            cell0.setCellValue("品类");
            cell1.setCellValue("新品种号编码");
            cell2.setCellValue("新品种号名称");
            cell3.setCellValue("类编");
            cell4.setCellValue("名称");
            cell5.setCellValue("数量合计");
            cell6.setCellValue("销售合计");
            cell7.setCellValue("毛利合计");
            cell8.setCellValue("1月数量");
            cell9.setCellValue("1月销售");
            cell10.setCellValue("1月毛利");
            cell11.setCellValue("2月数量");
            cell12.setCellValue("2月销售");
            cell13.setCellValue("2月毛利");
            cell14.setCellValue("3月数量");
            cell15.setCellValue("3月销售");
            cell16.setCellValue("3月毛利");
            cell17.setCellValue("4月数量");
            cell18.setCellValue("4月销售");
            cell19.setCellValue("4月毛利");
            cell20.setCellValue("5月数量");
            cell21.setCellValue("5月销售");
            cell22.setCellValue("5月毛利");
            cell23.setCellValue("6月数量");
            cell24.setCellValue("6月销售");
            cell25.setCellValue("6月毛利");
            cell26.setCellValue("7月数量");
            cell27.setCellValue("7月销售");
            cell28.setCellValue("7月毛利");
            cell29.setCellValue("8月数量");
            cell30.setCellValue("8月销售");
            cell31.setCellValue("8月毛利");
            cell32.setCellValue("9月数量");
            cell33.setCellValue("9月销售");
            cell34.setCellValue("9月毛利");
            cell35.setCellValue("10月数量");
            cell36.setCellValue("10月销售");
            cell37.setCellValue("10月毛利");
            cell38.setCellValue("11月数量");
            cell39.setCellValue("11月销售");
            cell40.setCellValue("11月毛利");
            cell41.setCellValue("12月数量");
            cell42.setCellValue("12月销售");
            cell43.setCellValue("12月毛利");

            cell0.setCellStyle(cellStyle2);
            cell1.setCellStyle(cellStyle2);
            cell2.setCellStyle(cellStyle2);
            cell3.setCellStyle(cellStyle2);
            cell4.setCellStyle(cellStyle2);
            cell5.setCellStyle(cellStyle2);
            cell6.setCellStyle(cellStyle2);
            cell7.setCellStyle(cellStyle2);
            cell8.setCellStyle(cellStyle2);
            cell9.setCellStyle(cellStyle2);
            cell10.setCellStyle(cellStyle2);
            cell11.setCellStyle(cellStyle2);
            cell12.setCellStyle(cellStyle2);
            cell13.setCellStyle(cellStyle2);
            cell14.setCellStyle(cellStyle2);
            cell15.setCellStyle(cellStyle2);
            cell16.setCellStyle(cellStyle2);
            cell17.setCellStyle(cellStyle2);
            cell18.setCellStyle(cellStyle2);
            cell19.setCellStyle(cellStyle2);
            cell20.setCellStyle(cellStyle2);
            cell21.setCellStyle(cellStyle2);
            cell22.setCellStyle(cellStyle2);
            cell23.setCellStyle(cellStyle2);
            cell24.setCellStyle(cellStyle2);
            cell25.setCellStyle(cellStyle2);
            cell26.setCellStyle(cellStyle2);
            cell27.setCellStyle(cellStyle2);
            cell28.setCellStyle(cellStyle2);
            cell29.setCellStyle(cellStyle2);
            cell30.setCellStyle(cellStyle2);
            cell31.setCellStyle(cellStyle2);
            cell32.setCellStyle(cellStyle2);
            cell33.setCellStyle(cellStyle2);
            cell34.setCellStyle(cellStyle2);
            cell35.setCellStyle(cellStyle2);
            cell36.setCellStyle(cellStyle2);
            cell37.setCellStyle(cellStyle2);
            cell38.setCellStyle(cellStyle2);
            cell39.setCellStyle(cellStyle2);
            cell40.setCellStyle(cellStyle2);
            cell41.setCellStyle(cellStyle2);
            cell42.setCellStyle(cellStyle2);
            cell43.setCellStyle(cellStyle2);
            // 第三行，数据开始
            cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);
            // 从数据库中取数据
            DeptSale lds_tmp = new DeptSale();
            lds_tmp.setYear(ls_year);
            SqlMapClient sqlMap = MySqlMapConfig.getSqlMapInstance();

            List Data = null;
            try {
                int li_year = Integer.parseInt(ls_year);
                if (li_year < 2010) {
                    Data = sqlMap.queryForList("getPqSaleYearOldMx", lds_tmp);
                } else {
                    Data = sqlMap.queryForList("getPqSaleYearMx", lds_tmp);
                }



            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
            li_rowcount = Data.size();
            for (int i = 0; i < li_rowcount; i++) {

                ii = setExcelAllCell(ii, cellStyle3, sheet, Data, i);
            }
            /*
             * //在索引0的位置创建单元格（左上端 HSSFCell cell = row.createCell((short)0); //
             * 定义单元格为字符串类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); //
             * 在单元格中输入一些内容 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
             * cell.setCellValue("这也是测试"); //下一行 row=sheet.createRow( (short)1);
             * cell=row.createCell( (short)0); //设置单元格内容 cell.setCellValue(
             * "Powered by 夏天"); //下一行 row=sheet.createRow( (short)1);
             * cell=row.createCell( (short)1); //设置单元格内容 cell.setCellValue(
             * "kkkk by gggg");
             */
            // 新建一输出文件流
            FileOutputStream fOut = new FileOutputStream(outputFile);
            // 把相应的Excel 工作簿存盘
            workbook.write(fOut);
            fOut.flush();
            // 操作结束，关闭文件
            fOut.close();
        // System.out.println("文件生成...");
        } catch (Exception e) {
            System.out.println("已运行 xlCreate() : " + e);
        }
    }

    /**
     * set excel cell data
     * @param ii
     * @param cellStyle3
     * @param sheet
     * @param Data
     * @param i
     * @return
     */
    private short setExcelCell(short ii, HSSFCellStyle cellStyle3, HSSFSheet sheet, List Data, int i) {
        HSSFCell cell0;
        HSSFCell cell1;
        HSSFCell cell2;
        HSSFCell cell3;
        HSSFCell cell4;
        HSSFCell cell5;
        HSSFCell cell6;
        HSSFCell cell7;
        HSSFCell cell8;
        HSSFCell cell9;
        HSSFCell cell10;
        HSSFCell cell11;
        HSSFCell cell12;
        HSSFCell cell13;
        HSSFCell cell14;
        HSSFCell cell15;
        HSSFCell cell16;
        HSSFCell cell17;

        HSSFRow row;
        String ls_lb;
        String ls_mc;
        Double ld_zje;
        Double ld_je1;
        Double ld_je2;
        Double ld_je3;
        Double ld_je4;
        Double ld_je5;
        Double ld_je6;
        Double ld_je7;
        Double ld_je8;
        Double ld_je9;
        Double ld_je10;
        Double ld_je11;
        Double ld_je12;
        String ls_xpzhbm;
        String ls_xpzhmc;

        ls_lb = ((DeptSale) Data.get(i)).getLb();
        ls_mc = ((DeptSale) Data.get(i)).getMc();

        ld_je1 = ((DeptSale) Data.get(i)).getZje1();
        ld_je2 = ((DeptSale) Data.get(i)).getZje2();
        ld_je3 = ((DeptSale) Data.get(i)).getZje3();
        ld_je4 = ((DeptSale) Data.get(i)).getZje4();
        ld_je5 = ((DeptSale) Data.get(i)).getZje5();
        ld_je6 = ((DeptSale) Data.get(i)).getZje6();
        ld_je7 = ((DeptSale) Data.get(i)).getZje7();
        ld_je8 = ((DeptSale) Data.get(i)).getZje8();
        ld_je9 = ((DeptSale) Data.get(i)).getZje9();
        ld_je10 = ((DeptSale) Data.get(i)).getZje10();
        ld_je11 = ((DeptSale) Data.get(i)).getZje11();
        ld_je12 = ((DeptSale) Data.get(i)).getZje12();
        ld_zje = new Double(ld_je1.doubleValue() + ld_je2.doubleValue() + ld_je3.doubleValue() + ld_je4.doubleValue() + ld_je5.doubleValue() + ld_je6.doubleValue() + ld_je7.doubleValue() + ld_je8.doubleValue() + ld_je9.doubleValue() + ld_je10.doubleValue() + ld_je11.doubleValue() + ld_je12.doubleValue());
        ls_xpzhbm = ((DeptSale) Data.get(i)).getXpzhbm();
        ls_xpzhmc = ((DeptSale) Data.get(i)).getXpzhmc();
        //如果全年合计为0则跳过该记录
        if (ld_zje.doubleValue() == 0) {
            return ii;
        }
        // System.out.println("lb:"+ls_lb);
        ii++;
        row = sheet.createRow(ii);
        cell0 = row.createCell((short) 0);
        cell1 = row.createCell((short) 1);
        cell2 = row.createCell((short) 2);
        cell3 = row.createCell((short) 3);
        cell4 = row.createCell((short) 4);
        cell5 = row.createCell((short) 5);
        cell6 = row.createCell((short) 6);
        cell7 = row.createCell((short) 7);
        cell8 = row.createCell((short) 8);
        cell9 = row.createCell((short) 9);
        cell10 = row.createCell((short) 10);
        cell11 = row.createCell((short) 11);
        cell12 = row.createCell((short) 12);
        cell13 = row.createCell((short) 13);
        cell14 = row.createCell((short) 14);
        cell15 = row.createCell((short) 15);
        cell16 = row.createCell((short) 16);
        cell17 = row.createCell((short) 17);

        cell0.setEncoding(HSSFCell.ENCODING_UTF_16);// 这里是设置编码保证中文正常显示
        cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell4.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell5.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell9.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell10.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell11.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell16.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell17.setEncoding(HSSFCell.ENCODING_UTF_16);


        cell0.setCellValue(ls_xpzhbm.substring(0, 1));
        cell1.setCellValue(ls_xpzhbm);
        cell2.setCellValue(ls_xpzhmc);
        cell3.setCellValue(ls_lb);
        cell4.setCellValue(ls_mc);
        cell5.setCellValue(ld_zje.doubleValue());
        cell6.setCellValue(ld_je1.doubleValue());
        cell7.setCellValue(ld_je2.doubleValue());
        cell8.setCellValue(ld_je3.doubleValue());
        cell9.setCellValue(ld_je4.doubleValue());
        cell10.setCellValue(ld_je5.doubleValue());
        cell11.setCellValue(ld_je6.doubleValue());
        cell12.setCellValue(ld_je7.doubleValue());
        cell13.setCellValue(ld_je8.doubleValue());
        cell14.setCellValue(ld_je9.doubleValue());
        cell15.setCellValue(ld_je10.doubleValue());
        cell16.setCellValue(ld_je11.doubleValue());
        cell17.setCellValue(ld_je12.doubleValue());


        cell0.setCellStyle(cellStyle3);
        cell1.setCellStyle(cellStyle3);
        cell2.setCellStyle(cellStyle3);
        cell3.setCellStyle(cellStyle3);
        cell4.setCellStyle(cellStyle3);
        cell5.setCellStyle(cellStyle3);
        cell6.setCellStyle(cellStyle3);
        cell7.setCellStyle(cellStyle3);
        cell8.setCellStyle(cellStyle3);
        cell9.setCellStyle(cellStyle3);
        cell10.setCellStyle(cellStyle3);
        cell11.setCellStyle(cellStyle3);
        cell12.setCellStyle(cellStyle3);
        cell13.setCellStyle(cellStyle3);
        cell14.setCellStyle(cellStyle3);
        cell15.setCellStyle(cellStyle3);
        cell16.setCellStyle(cellStyle3);
        cell17.setCellStyle(cellStyle3);
        return ii;
    }

    private short setExcelAllCell(short ii, HSSFCellStyle cellStyle3, HSSFSheet sheet, List Data, int i) {
        HSSFCell cell0;
        HSSFCell cell1;
        HSSFCell cell2;
        HSSFCell cell3;
        HSSFCell cell4;
        HSSFCell cell5;
        HSSFCell cell6;
        HSSFCell cell7;
        HSSFCell cell8;
        HSSFCell cell9;
        HSSFCell cell10;
        HSSFCell cell11;
        HSSFCell cell12;
        HSSFCell cell13;
        HSSFCell cell14;
        HSSFCell cell15;
        HSSFCell cell16;
        HSSFCell cell17;
        HSSFCell cell18;
        HSSFCell cell19;
        HSSFCell cell20;
        HSSFCell cell21;
        HSSFCell cell22;
        HSSFCell cell23;
        HSSFCell cell24;
        HSSFCell cell25;
        HSSFCell cell26;
        HSSFCell cell27;
        HSSFCell cell28;
        HSSFCell cell29;
        HSSFCell cell30;
        HSSFCell cell31;
        HSSFCell cell32;
        HSSFCell cell33;
        HSSFCell cell34;
        HSSFCell cell35;
        HSSFCell cell36;
        HSSFCell cell37;
        HSSFCell cell38;
        HSSFCell cell39;
        HSSFCell cell40;
        HSSFCell cell41;
        HSSFCell cell42;
        HSSFCell cell43;
        HSSFRow row;
        String ls_lb;
        String ls_mc;
        Double ld_zje; //总金额
        Double ld_zsl; //总数量
        Double ld_zml; //总毛利
        Double ld_je1;
        Double ld_je2;
        Double ld_je3;
        Double ld_je4;
        Double ld_je5;
        Double ld_je6;
        Double ld_je7;
        Double ld_je8;
        Double ld_je9;
        Double ld_je10;
        Double ld_je11;
        Double ld_je12;
        Double ld_sl1;
        Double ld_sl2;
        Double ld_sl3;
        Double ld_sl4;
        Double ld_sl5;
        Double ld_sl6;
        Double ld_sl7;
        Double ld_sl8;
        Double ld_sl9;
        Double ld_sl10;
        Double ld_sl11;
        Double ld_sl12;
        Double ld_ml1;
        Double ld_ml2;
        Double ld_ml3;
        Double ld_ml4;
        Double ld_ml5;
        Double ld_ml6;
        Double ld_ml7;
        Double ld_ml8;
        Double ld_ml9;
        Double ld_ml10;
        Double ld_ml11;
        Double ld_ml12;

        String ls_xpzhbm;
        String ls_xpzhmc;

        ls_lb = ((DeptSale) Data.get(i)).getLb();
        ls_mc = ((DeptSale) Data.get(i)).getMc();
        //销售金额
        ld_je1 = ((DeptSale) Data.get(i)).getZje1();
        ld_je2 = ((DeptSale) Data.get(i)).getZje2();
        ld_je3 = ((DeptSale) Data.get(i)).getZje3();
        ld_je4 = ((DeptSale) Data.get(i)).getZje4();
        ld_je5 = ((DeptSale) Data.get(i)).getZje5();
        ld_je6 = ((DeptSale) Data.get(i)).getZje6();
        ld_je7 = ((DeptSale) Data.get(i)).getZje7();
        ld_je8 = ((DeptSale) Data.get(i)).getZje8();
        ld_je9 = ((DeptSale) Data.get(i)).getZje9();
        ld_je10 = ((DeptSale) Data.get(i)).getZje10();
        ld_je11 = ((DeptSale) Data.get(i)).getZje11();
        ld_je12 = ((DeptSale) Data.get(i)).getZje12();
        //销售数量
        ld_sl1 = ((DeptSale) Data.get(i)).getZsl1();
        ld_sl2 = ((DeptSale) Data.get(i)).getZsl2();
        ld_sl3 = ((DeptSale) Data.get(i)).getZsl3();
        ld_sl4 = ((DeptSale) Data.get(i)).getZsl4();
        ld_sl5 = ((DeptSale) Data.get(i)).getZsl5();
        ld_sl6 = ((DeptSale) Data.get(i)).getZsl6();
        ld_sl7 = ((DeptSale) Data.get(i)).getZsl7();
        ld_sl8 = ((DeptSale) Data.get(i)).getZsl8();
        ld_sl9 = ((DeptSale) Data.get(i)).getZsl9();
        ld_sl10 = ((DeptSale) Data.get(i)).getZsl10();
        ld_sl11 = ((DeptSale) Data.get(i)).getZsl11();
        ld_sl12 = ((DeptSale) Data.get(i)).getZsl12();
        //毛利额
        ld_ml1 = ((DeptSale) Data.get(i)).getMle1();
        ld_ml2 = ((DeptSale) Data.get(i)).getMle2();
        ld_ml3 = ((DeptSale) Data.get(i)).getMle3();
        ld_ml4 = ((DeptSale) Data.get(i)).getMle4();
        ld_ml5 = ((DeptSale) Data.get(i)).getMle5();
        ld_ml6 = ((DeptSale) Data.get(i)).getMle6();
        ld_ml7 = ((DeptSale) Data.get(i)).getMle7();
        ld_ml8 = ((DeptSale) Data.get(i)).getMle8();
        ld_ml9 = ((DeptSale) Data.get(i)).getMle9();
        ld_ml10 = ((DeptSale) Data.get(i)).getMle10();
        ld_ml11 = ((DeptSale) Data.get(i)).getMle11();
        ld_ml12 = ((DeptSale) Data.get(i)).getMle12();

        ld_zje = new Double(ld_je1.doubleValue() + ld_je2.doubleValue() + ld_je3.doubleValue() + ld_je4.doubleValue() + ld_je5.doubleValue() + ld_je6.doubleValue() + ld_je7.doubleValue() + ld_je8.doubleValue() + ld_je9.doubleValue() + ld_je10.doubleValue() + ld_je11.doubleValue() + ld_je12.doubleValue());
        ld_zsl = new Double(ld_sl1.doubleValue() + ld_sl2.doubleValue() + ld_sl3.doubleValue() + ld_sl4.doubleValue() + ld_sl5.doubleValue() + ld_sl6.doubleValue() + ld_sl7.doubleValue() + ld_sl8.doubleValue() + ld_sl9.doubleValue() + ld_sl10.doubleValue() + ld_sl11.doubleValue() + ld_sl12.doubleValue());
        ld_zml = new Double(ld_ml1.doubleValue() + ld_ml2.doubleValue() + ld_ml3.doubleValue() + ld_ml4.doubleValue() + ld_ml5.doubleValue() + ld_ml6.doubleValue() + ld_ml7.doubleValue() + ld_ml8.doubleValue() + ld_ml9.doubleValue() + ld_ml10.doubleValue() + ld_ml11.doubleValue() + ld_ml12.doubleValue());
        ls_xpzhbm = ((DeptSale) Data.get(i)).getXpzhbm();
        ls_xpzhmc = ((DeptSale) Data.get(i)).getXpzhmc();
        //如果全年合计为0则跳过该记录
        if (ld_zje.doubleValue() == 0) {
            return ii;
        }
        // System.out.println("lb:"+ls_lb);
        ii++;
        row = sheet.createRow(ii);
        cell0 = row.createCell((short) 0);
        cell1 = row.createCell((short) 1);
        cell2 = row.createCell((short) 2);
        cell3 = row.createCell((short) 3);
        cell4 = row.createCell((short) 4);
        cell5 = row.createCell((short) 5);
        cell6 = row.createCell((short) 6);
        cell7 = row.createCell((short) 7);
        cell8 = row.createCell((short) 8);
        cell9 = row.createCell((short) 9);
        cell10 = row.createCell((short) 10);
        cell11 = row.createCell((short) 11);
        cell12 = row.createCell((short) 12);
        cell13 = row.createCell((short) 13);
        cell14 = row.createCell((short) 14);
        cell15 = row.createCell((short) 15);
        cell16 = row.createCell((short) 16);
        cell17 = row.createCell((short) 17);
        cell18 = row.createCell((short) 18);
        cell19 = row.createCell((short) 19);
        cell20 = row.createCell((short) 20);
        cell21 = row.createCell((short) 21);
        cell22 = row.createCell((short) 22);
        cell23 = row.createCell((short) 23);
        cell24 = row.createCell((short) 24);
        cell25 = row.createCell((short) 25);
        cell26 = row.createCell((short) 26);
        cell27 = row.createCell((short) 27);
        cell28 = row.createCell((short) 28);
        cell29 = row.createCell((short) 29);
        cell30 = row.createCell((short) 30);
        cell31 = row.createCell((short) 31);
        cell32 = row.createCell((short) 32);
        cell33 = row.createCell((short) 33);
        cell34 = row.createCell((short) 34);
        cell35 = row.createCell((short) 35);
        cell36 = row.createCell((short) 36);
        cell37 = row.createCell((short) 37);
        cell38 = row.createCell((short) 38);
        cell39 = row.createCell((short) 39);
        cell40 = row.createCell((short) 40);
        cell41 = row.createCell((short) 41);
        cell42 = row.createCell((short) 42);
        cell43 = row.createCell((short) 43);

        cell0.setEncoding(HSSFCell.ENCODING_UTF_16);// 这里是设置编码保证中文正常显示
        cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell4.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell5.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell8.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell9.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell10.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell11.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell12.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell13.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell14.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell15.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell16.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell17.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell18.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell19.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell20.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell21.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell22.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell23.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell24.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell25.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell26.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell27.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell28.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell29.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell30.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell31.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell32.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell33.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell34.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell35.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell36.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell37.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell38.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell39.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell40.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell41.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell42.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell43.setEncoding(HSSFCell.ENCODING_UTF_16);

        //商品信息
        cell0.setCellValue(ls_xpzhbm.substring(0, 1));
        cell1.setCellValue(ls_xpzhbm);
        cell2.setCellValue(ls_xpzhmc);
        cell3.setCellValue(ls_lb);
        cell4.setCellValue(ls_mc);

        //汇总的项目
        cell5.setCellValue(ld_zsl.doubleValue());
        cell6.setCellValue(ld_zje.doubleValue());
        cell7.setCellValue(ld_zml.doubleValue());
        //具体月份由1到12
        cell8.setCellValue(ld_sl1.doubleValue());
        cell9.setCellValue(ld_je1.doubleValue());
        cell10.setCellValue(ld_ml1.doubleValue());
        cell11.setCellValue(ld_sl2.doubleValue());
        cell12.setCellValue(ld_je2.doubleValue());
        cell13.setCellValue(ld_ml2.doubleValue());
        cell14.setCellValue(ld_sl3.doubleValue());
        cell15.setCellValue(ld_je3.doubleValue());
        cell16.setCellValue(ld_ml3.doubleValue());
        cell17.setCellValue(ld_sl4.doubleValue());
        cell18.setCellValue(ld_je4.doubleValue());
        cell19.setCellValue(ld_ml4.doubleValue());
        cell20.setCellValue(ld_sl5.doubleValue());
        cell21.setCellValue(ld_je5.doubleValue());
        cell22.setCellValue(ld_ml5.doubleValue());
        cell23.setCellValue(ld_sl6.doubleValue());
        cell24.setCellValue(ld_je6.doubleValue());
        cell25.setCellValue(ld_ml6.doubleValue());
        cell26.setCellValue(ld_sl7.doubleValue());
        cell27.setCellValue(ld_je7.doubleValue());
        cell28.setCellValue(ld_ml7.doubleValue());
        cell29.setCellValue(ld_sl8.doubleValue());
        cell30.setCellValue(ld_je8.doubleValue());
        cell31.setCellValue(ld_ml8.doubleValue());
        cell32.setCellValue(ld_sl9.doubleValue());
        cell33.setCellValue(ld_je9.doubleValue());
        cell34.setCellValue(ld_ml9.doubleValue());
        cell35.setCellValue(ld_sl10.doubleValue());
        cell36.setCellValue(ld_je10.doubleValue());
        cell37.setCellValue(ld_ml10.doubleValue());
        cell38.setCellValue(ld_sl11.doubleValue());
        cell39.setCellValue(ld_je11.doubleValue());
        cell40.setCellValue(ld_ml11.doubleValue());
        cell41.setCellValue(ld_sl12.doubleValue());
        cell42.setCellValue(ld_je12.doubleValue());
        cell43.setCellValue(ld_ml12.doubleValue());


        cell0.setCellStyle(cellStyle3);
        cell1.setCellStyle(cellStyle3);
        cell2.setCellStyle(cellStyle3);
        cell3.setCellStyle(cellStyle3);
        cell4.setCellStyle(cellStyle3);
        cell5.setCellStyle(cellStyle3);
        cell6.setCellStyle(cellStyle3);
        cell7.setCellStyle(cellStyle3);
        cell8.setCellStyle(cellStyle3);
        cell9.setCellStyle(cellStyle3);
        cell10.setCellStyle(cellStyle3);
        cell11.setCellStyle(cellStyle3);
        cell12.setCellStyle(cellStyle3);
        cell13.setCellStyle(cellStyle3);
        cell14.setCellStyle(cellStyle3);
        cell15.setCellStyle(cellStyle3);
        cell16.setCellStyle(cellStyle3);
        cell17.setCellStyle(cellStyle3);
        cell18.setCellStyle(cellStyle3);
        cell19.setCellStyle(cellStyle3);
        cell20.setCellStyle(cellStyle3);
        cell21.setCellStyle(cellStyle3);
        cell22.setCellStyle(cellStyle3);
        cell23.setCellStyle(cellStyle3);
        cell24.setCellStyle(cellStyle3);
        cell25.setCellStyle(cellStyle3);
        cell26.setCellStyle(cellStyle3);
        cell27.setCellStyle(cellStyle3);
        cell28.setCellStyle(cellStyle3);
        cell29.setCellStyle(cellStyle3);
        cell30.setCellStyle(cellStyle3);
        cell31.setCellStyle(cellStyle3);
        cell32.setCellStyle(cellStyle3);
        cell33.setCellStyle(cellStyle3);
        cell34.setCellStyle(cellStyle3);
        cell35.setCellStyle(cellStyle3);
        cell36.setCellStyle(cellStyle3);
        cell37.setCellStyle(cellStyle3);
        cell38.setCellStyle(cellStyle3);
        cell39.setCellStyle(cellStyle3);
        cell40.setCellStyle(cellStyle3);
        cell41.setCellStyle(cellStyle3);
        cell42.setCellStyle(cellStyle3);
        cell43.setCellStyle(cellStyle3);

        return ii;
    }

    public void readExcel() {
        try {
            // 创建对Excel工作簿文件的引用
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
                    fileToBeRead));
            // System.out
            // .println("===SheetsNum===" +
            // workbook.getNumberOfSheets());//获取sheet数
            for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {// 不等于空的话。有数据
                if (null != workbook.getSheetAt(numSheets)) {
                    HSSFSheet aSheet = workbook.getSheetAt(numSheets); // 获得一个sheet
                    for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {
                        if (null != aSheet.getRow(rowNumOfSheet)) {
                            HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
                            for (short cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {
                                if (null != aRow.getCell(cellNumOfRow)) {
                                    HSSFCell aCell = aRow.getCell(cellNumOfRow);
                                    System.out.println(aCell.getStringCellValue());
                                }
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("ReadExcelError" + e);
        }
    }

    public static void main(String[] args) {
        MakeDeptSale poi = new MakeDeptSale();
        poi.createExcel();
        System.out.println("文件已生成");
    }
}
